
import pandas as pd
import os
import warnings
warnings.filterwarnings('ignore', message='Workbook contains no default style')


# 配置
BASE_PATH = r'E:\每日'
RESULT_PATH = r'E:\每日结果'
CONFIG_PATH = r'E:\pycharmProject\data\配置表'

#  创建目录 , exist_ok=True:  目录已经存在则不抛出异常
os.makedirs(RESULT_PATH, exist_ok=True)

VALID_ORDER_STATUS = [
    '部分发货', '待发货', '待配货', '等待确认收货', '交易成功',
    '卖家已发货，等待买家确认', '已发货', '已发货，待签收', '已发货未签收',
    '已完成', '已支付', '已签收', '买家已付款，等待卖家发货', '等待出库',
    '(锁定)等待确认收货', '完成', '已发货，待收货', '已收货', '卖家部分发货',
    '买家已付款,等待卖家发货', '卖家已发货', '买家已付款', '发货即将超时',
    '待买家收货', '待卖家发货', '部分发货中'
]

REFUND_STATUS = [
    '退款成功', '退款完成', '已全额退款', '(删除)等待出库',
    '(删除)等待确认收货', '售后完成', '退货退款完成', '交易关闭'
]

EXCLUDE_ITEMS = ['抖音直播间赠品', 'logo定制电煮锅1个', '红色电煮锅1个', '赠品']

# 辅助函数
def read_excel_safe(file_path):
    """安全读取Excel"""
    try:
        return pd.read_excel(file_path)
    except Exception as e:
        print(f"❌ 读取失败: {file_path}")
        return pd.DataFrame()


def clean_dataframe(df):
    """数据清洗：去空格、处理日期、转换类型"""
    if df.empty:
        return df

    df = df.copy()
    df = df.map(lambda x: str(x).strip() if isinstance(x, str) else x)

    if '商家编码' in df.columns:
        df['商家编码'] = df['商家编码'].astype(str).str.strip()
        df.dropna(subset=['商家编码'], inplace=True)

    if '日期' in df.columns:
        df['日期'] = df['日期'].replace("", '1998-01-01 00:00:00')
        df['日期'] = pd.to_datetime(df['日期']).apply(lambda x: x.date())

    if '商品数量' in df.columns:
        df['商品数量'] = df['商品数量'].apply(lambda x: int(float(x)))

    if '订单应付金额' in df.columns:
        df['订单应付金额'] = df['订单应付金额'].apply(lambda x: float(x))

    if '退款金额' in df.columns:
        df['退款金额'] = df['退款金额'].apply(lambda x: float(x))

    return df



# 线上平台数据加载
class OnlineDataLoader:
    def __init__(self, base_path):
        self.base_path = base_path

    def load_all_data(self):
        """加载所有线上数据（JD、视频号去重用于销售额统计）"""
        print("正在加载线上数据...")

        data_list = [
            self.load_douyin_data(),
            self.load_jd_data(),
            self.load_pdd_data(),
            self.load_kuaishou_data(),
            self.load_other_platforms()
        ]
        # ignore_index=True 确保合并后的索引是连续的
        return pd.concat([d for d in data_list if not d.empty], ignore_index=True)

    def load_douyin_data(self):
        """抖音（4店）"""
        data_list = []
        for i in range(1, 5):
            df = read_excel_safe(f'{self.base_path}\\抖音{i}.xlsx')
            if not df.empty:
                df = df[['商家编码', '商品数量', '订单应付金额', '订单状态',
                         '售后状态', '订单提交时间', '平台实际承担优惠金额', '达人实际承担优惠金额']].copy()
                df['平台'] = f'抖音{i}'
                df['订单应付金额'] = (df['订单应付金额'] +
                                       df['平台实际承担优惠金额'] +
                                       df['达人实际承担优惠金额'])
                df = df[['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '订单提交时间', '平台']]
                df.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '日期', '平台']
                df['退款金额'] = df['订单应付金额']
                data_list.append(df)
        return pd.concat(data_list, ignore_index=True) if data_list else pd.DataFrame()

    def load_jd_data(self):
        """京东（2店，去重）"""
        jd1 = read_excel_safe(f'{self.base_path}\\JD.xlsx')
        jd2 = read_excel_safe(f'{self.base_path}\\JD1.xlsx')

        # 去重用于销售额统计
        jd1 = jd1.drop_duplicates(subset=['订单号']) if not jd1.empty else pd.DataFrame()
        jd2 = jd2.drop_duplicates(subset=['订单号']) if not jd2.empty else pd.DataFrame()

        if not jd1.empty:
            jd1['平台'] = '京东旗舰店'
        if not jd2.empty:
            jd2['平台'] = '京东旗舰店2'

        jd_all = pd.concat([jd1, jd2], ignore_index=True)
        if jd_all.empty:
            return pd.DataFrame()

        result = jd_all[['商家SKUID', '订购数量', '结算金额', '订单状态', '下单时间', '平台']].copy()
        result.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '日期', '平台']
        result['售后状态'] = result['订单状态']
        result['退款金额'] = result['订单应付金额']
        return result

    def load_pdd_data(self):
        """拼多多（6店）"""
        stores = {f'PDD{i}': f'拼多多{i}店' for i in [1, 2, 3, 4, 5, 6]}
        data_list = []

        for file_name, store_name in stores.items():
            df = read_excel_safe(f'{self.base_path}\\{file_name}.xlsx')
            if not df.empty:
                df = df[['商家编码-规格维度', '商品数量(件)', '商家实收金额(元)',
                         '订单状态', '售后状态', '支付时间']].copy()
                df['平台'] = store_name
                df.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '日期', '平台']
                df['退款金额'] = df['订单应付金额']
                data_list.append(df)

        return pd.concat(data_list, ignore_index=True) if data_list else pd.DataFrame()

    def load_kuaishou_data(self):
        """快手（3店）"""
        data_list = []
        for i in range(1, 4):
            df = read_excel_safe(f'{self.base_path}\\快手{i}.xlsx')
            if not df.empty:
                df = df[['SKU编码', '成交数量', '实付款', '订单状态', '售后状态', '订单创建时间']].copy()
                df['平台'] = f'快手{i}'
                df.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '日期', '平台']
                df['订单应付金额'] = df['订单应付金额'].apply(lambda x: float(str(x).replace('¥', '')))
                df['退款金额'] = df['订单应付金额']
                data_list.append(df)
        return pd.concat(data_list, ignore_index=True) if data_list else pd.DataFrame()

    def load_other_platforms(self):
        """其他平台：小红书、视频号、天猫、淘宝"""
        all_data = []

        # 小红书
        xhs = read_excel_safe(f'{self.base_path}\\小红书.xlsx')
        if not xhs.empty:
            xhs = xhs[['SKU规格', 'SKU件数', '商家应收金额(元)（支付金额）',
                       '订单状态', '售后状态', '订单创建时间']].copy()
            xhs['平台'] = '小红书'
            xhs.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '日期', '平台']
            xhs['退款金额'] = xhs['订单应付金额']
            all_data.append(xhs)

        # 视频号（去重）
        sph = read_excel_safe(f'{self.base_path}\\视频号.xlsx')
        if not sph.empty:
            sph = sph.drop_duplicates(subset=['订单号'])
            sph = sph[['SKU编码(自定义)', '商品数量', '订单实际支付金额',
                       '订单状态', '商品售后', '商品已退款金额', '订单下单时间']].copy()
            sph['平台'] = '视频号'
            sph.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '退款金额', '日期', '平台']
            all_data.append(sph)

        # 天猫旗舰店
        tm = read_excel_safe(f'{self.base_path}\\天猫.xlsx')
        if not tm.empty:
            tm = tm[['商家编码', '购买数量', '买家实付金额', '订单状态', '退款状态', '退款金额', '订单创建时间']].copy()
            tm['平台'] = '天猫旗舰店'
            tm['退款金额'] = tm['退款金额'].apply(lambda x: float(str(x).replace('无退款申请', '0')))
            tm.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '退款金额', '日期', '平台']
            tm = tm[tm['商家编码'].notna() & (tm['商家编码'] != '')]
            all_data.append(tm)

        # 淘宝系（3个）
        for file, name in [('淘宝买菜', '淘宝买菜'), ('淘工厂', '淘工厂'), ('淘工厂2', '淘宝买菜')]:
            df = read_excel_safe(f'{self.base_path}\\{file}.xlsx')
            if not df.empty:
                df = df[['商家编码', '宝贝数量', '子单实际支付金额', '订单状态', '退款金额', '订单创建时间']].copy()
                df['平台'] = name
                df.columns = ['商家编码', '商品数量', '订单应付金额', '订单状态', '退款金额', '日期', '平台']
                df['售后状态'] = df['订单状态']
                all_data.append(df)

        return pd.concat(all_data, ignore_index=True) if all_data else pd.DataFrame()



# 超市数据加载
class OfflineDataLoader:
    def __init__(self, base_path, config_path):
        self.base_path = base_path
        self.config_path = config_path

    def load_all_data(self):
        """加载抖超、猫超、京东自营"""
        print("正在加载超市数据...")
        all_data = []

        # 抖超
        douchao = read_excel_safe(f'{self.base_path}\\抖超.xlsx')
        if not douchao.empty:
            douchao['平台'] = '抖音超市'
            douchao['日期'] = douchao['日期'].apply(lambda x: str(x)[:8])
            douchao = douchao[['日期', '支付货品件数', '支付GMV', '平台']].copy()
            douchao.columns = ['日期', '销售单量', '销售额', '平台']
            douchao['退款金额'] = 0
            all_data.append(douchao)

        # 猫超
        maochao = read_excel_safe(f'{self.base_path}\\猫超.xlsx')
        if not maochao.empty:
            maochao['平台'] = '猫超'
            maochao['日期'] = maochao['统计日期'].apply(lambda x: str(x)[:8])
            maochao = maochao[['日期', '支付子订单数(剔退款)',
                               '支付金额(剔退款)', '退款成功金额', '平台']].copy()
            maochao.columns = ['日期', '销售单量', '销售额', '退款金额', '平台']
            all_data.append(maochao)

        # 京东自营（2店）
        for i, name in [(1, '京东自营1'), (2, '京东自营2')]:
            ziying = read_excel_safe(f'{self.base_path}\\自营{i}.xlsx')
            if not ziying.empty:
                if i == 2:
                    ziying = ziying[~ziying['SKU'].isin([100095090135, 100111765304, 100111765306, 100122053209])]

                ziying['平台'] = name
                ziying = ziying[['时间', '成交单量', '成交金额', '平台']].copy()
                ziying.columns = ['日期', '销售单量', '销售额', '平台']
                ziying['退款金额'] = 0
                all_data.append(ziying)

        return pd.concat(all_data, ignore_index=True) if all_data else pd.DataFrame()



# 数据处理
class DataProcessor:
    @staticmethod
    def filter_valid_orders(df):
        """筛选有效订单"""
        if df.empty:
            return df
        return df[
            (df['订单状态'].isin(VALID_ORDER_STATUS)) &
            (~df['售后状态'].isin(REFUND_STATUS)) &
            (df['订单应付金额'] >= 0.01) &
            (~df['商家编码'].isin(EXCLUDE_ITEMS))
            ].copy()

    @staticmethod
    def filter_refund_orders(df):
        """筛选退款订单"""
        return df[df['售后状态'].isin(REFUND_STATUS)].copy() if not df.empty else df

    @staticmethod
    def calculate_sales_summary(sales_df, refund_df):
        """计算销售汇总"""
        sales_summary = sales_df.groupby(['日期', '平台']).agg(
            销售额=('订单应付金额', 'sum'),
            销售单量=('商品数量', 'count')
        ).reset_index()

        refund_summary = refund_df.groupby(['日期', '平台']).agg(
            退款金额=('退款金额', 'sum')
        ).reset_index()

        result = pd.merge(sales_summary, refund_summary, on=['日期', '平台'], how='outer')
        result.fillna(0, inplace=True)
        result['总销售额'] = result['销售额'] + result['退款金额']
        return result



# 主流程

def main():
    print("=" * 80)
    print("电商平台销售数据分析系统")
    print("=" * 80)

    # 1. 加载线上数据
    online_loader = OnlineDataLoader(BASE_PATH)
    sales_data = clean_dataframe(online_loader.load_all_data())

    # 2. 加载超市数据
    offline_loader = OfflineDataLoader(BASE_PATH, CONFIG_PATH)
    offline_data = offline_loader.load_all_data()

    # 3. 筛选订单
    processor = DataProcessor()
    valid_sales = processor.filter_valid_orders(sales_data)
    refund_orders = processor.filter_refund_orders(sales_data)

    # 4. 计算销售汇总
    print("\n正在计算销售汇总...")
    online_summary = processor.calculate_sales_summary(valid_sales, refund_orders)

    # 5. 合并线上线下数据
    if not offline_data.empty:
        offline_summary = offline_data.groupby(['日期', '平台']).agg(
            销售额=('销售额', 'sum'),
            销售单量=('销售单量', 'sum'),
            退款金额=('退款金额', 'sum')
        ).reset_index()
        offline_summary['总销售额'] = offline_summary['销售额'] + offline_summary['退款金额']
        final_summary = pd.concat([online_summary, offline_summary], ignore_index=True)
    else:
        final_summary = online_summary

    # 6. 保存结果
    final_summary.to_excel(f'{RESULT_PATH}\\1_销售额汇总.xlsx', index=False)
    print("✅ 销售额汇总已保存")

    # 7. 统计报告
    print("\n" + "=" * 80)
    print("✅ 处理完成！")
    print("=" * 80)
    print(f"\n📊 数据概览:")
    print(f"   线上有效订单数: {len(valid_sales):,}")
    print(f"   线上退款订单数: {len(refund_orders):,}")
    print(f"   线上销售额: ¥{valid_sales['订单应付金额'].sum():,.2f}")
    print(f"   线上退款额: ¥{refund_orders['退款金额'].sum():,.2f}")

    if not offline_data.empty:
        print(f"   超市销售额: ¥{offline_data['销售额'].sum():,.2f}")

    print(f"\n   总销售额: ¥{final_summary['总销售额'].sum():,.2f}")


if __name__ == '__main__':
    try:
        main()
    except Exception as e:
        print(f"\n❌ 运行出错: {e}")
        import traceback
        traceback.print_exc()

